import multiprocessing
import time

import MySQLdb
import requests
from dbutils.pooled_db import PooledDB, SharedDBConnection
from fake_useragent import UserAgent
from lxml import etree

baseurl = 'https://www.kuaidaili.com/free/inha/'
ua = UserAgent(path='./fake_useragent_0.1.11.json')
process_num = 50
timeout = 20
https_url = 'https://httpbin.org/ip'
http_url = 'http://httpbin.org/ip'


class IpItem:

    def __init__(self, ip, port, anonymity, type,  location, speed, vertifytime):
        self.ip = ip
        self.port = port
        self.anonymity = anonymity
        self.type = type
        self.location = location
        self.speed = speed
        self.vertifytime = vertifytime
        self.responsetime = None


class MysqlOps:
    host = 'localhost'
    user = 'root'
    port = 3306
    pasword = '123456'
    db = 'db2'
    charset = 'utf8'

    pool = None
    limit_count = 3  # 最低预启动数据库连接数量

    def __init__(self):
        self.pool = PooledDB(MySQLdb, self.limit_count, host=self.host, user=self.user, passwd=self.pasword, db=self.db,
                             port=self.port, charset=self.charset)

    def clear(self, tablename=None):
        try:
            sql = 'truncate table %s' % tablename
            connection = self.pool.connection()
            cursor = connection.cursor()
            cursor.execute(sql)
            connection.commit()
            print('truncate table %s successfully' % tablename)
        except Exception:
            connection.rollback()
            print('truncate table %s failed' % tablename)

    def select(self, sql):
        connection = self.pool.connection()
        cursor = connection.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        cursor.close()
        connection.close()
        return result

    def insert(self,  sql):
        connection = self.pool.connection()
        cursor = connection.cursor()
        try:
            cursor.execute(sql)
            connection.commit()
            print("Inserted successfully")
        except Exception as err:
            connection.rollback()
            print("Inserted failed")
        finally:
            cursor.close()
            connection.close()


def get_IpItem(url):
    datalist = []
    with requests.get(url, headers={
        'User-Agent': ua.random
    }) as response:
        html = etree.HTML(response.text)
        trs = html.xpath('//*[@id="list"]/table/tbody/tr')
        for tr in trs:
            tds_text = tr.xpath('.//td/text()')
            try:
                item = IpItem(*tds_text)
                print('ip catch %s:%s' % (item.ip, item.port))
                datalist.append(item)
            except Exception as e:
                print(e)
        return datalist


def check_IpItem(item):
    try:
        if 'HTTPS' in item.type:
            test_url = https_url
            proxies = {
                # 注意格式为{https : http}
                'http': '%s:%s' % (item.ip, item.port),
                'https': '%s:%s' % (item.ip, item.port)
            }
        else:
            test_url = http_url
            proxies = {
                'http': '%s:%s' % (item.ip, item.port)
            }
        headers = {
            'User-Agent': ua.random
        }
        with requests.get(url=test_url, headers=headers, proxies=proxies, timeout=timeout) as response:
            response.raise_for_status()
            item.responsetime = response.elapsed.total_seconds()
            print('%s:%s is useful' % (item.ip, item.port))
            return True
    except Exception as e:
        # print(str(e).split('/')[-1].split(',')[-1])
        print('%s is bad ip' % item.ip)
        # print(str(e))
        return False


def check_IpItem_and_save(item):
    if check_IpItem(item):
        save_IpItem(item)
    else:
        pass


'''
#TODOMysqlOps 无法序列化
无法作为线程池任务的传递参数
'''
mysql_server = MysqlOps()


def save_IpItem(item):
    sql = 'insert into ippool values("%s", "%s","%s", "%s","%s","%s","%s","%f")' % (
        item.ip, item.port, item.anonymity, item.type,  item.location, item.speed, item.vertifytime, item.responsetime)
    mysql_server.insert(sql)


def print_IpItem(url):
    print(url)


def main():
    itemlists = []
    urls = [f'{baseurl}{i}' for i in range(1, 11)]
    pool = multiprocessing.Pool(processes=process_num)

    for url in urls:
        itemlists.append(get_IpItem(url))
        time.sleep(1)
    items = sum(itemlists, [])  # 将嵌套列表转化为单层列表
    pool.map(check_IpItem_and_save, items)


if __name__ == '__main__':
    mysql_server.clear('ippool')
    main()
